Cyber Security

Deploying C2 and EDR Analysis using Limacharlie and Sysmon
Network Traffic Analysis Tool using Python
Phishing Email Detection Tool using Python

Network Engineering

Secure AWS VPC Architecture and S3 Integration
Secure Enterprise Network Implementation using Cisco Packet Tracer

Data Analyst

Full-Stack Data Warehouse with Medallion Architecture using SQL
Power BI - IT Service Desk Dashboard
Power BI - IT Service Desk Ticket Dashboard
Power BI - IT Service Desk Ticket Dashboard

Power BI Dashboard Setup Guide

This guide will take you through setting up a Power BI dashboard from scratch, ensuring you understand the entire process, from importing data to creating meaningful visualizations.

1. Open Power BI

  • Launch Microsoft Power BI Desktop on your computer.

  • If you don’t have Power BI installed, download it from the Microsoft website.

2. Import Data into Power BI

  • Click on "Home" → "Get Data".

  • Select "Excel Workbook" if your data is in an Excel file.
    (Other common sources: SQL Server, CSV, SharePoint, Power BI datasets, and APIs.)

  • Navigate to your dataset (e.g., "ID Ticket Analysis.xlsx") and click "Open".

RAW DATASET TABLES :

  1. **IT_Agents:
    78c7d2dac4a418930bb1518c646ee1a3.png
    **
  2. **Tickets 1
    1bfe0dc9f4b255ae0bf7c8624ea88856.png
    **

3. Select Data Tables

  • A navigator window will open, displaying available tables in the Excel file.

  • Select the relevant tables:

    • IT_Agents Table

    • Tickets 1 Table

4. Transform Data in Power Query Editor

  • Click "Transform Data" before loading the tables.

  • Rename tables using the Properties column for clarity:
    780852357dcdf65f66bd3e465830e4a2.png

    • Rename "Table1" to "IT_Agents".

    • Rename "Table2" to "Tickets".

TRANSFORM Tickets Table :

  • Severity column has two values  a numeric one and then a class is assigned to the column as well.We shall split this column into two separate column using the SPLIT COLUMN  function.
    a8c347f6601384850ef3f22e95047d6a.png
    a44b96fc32b7eefd3ca5ab19ea4df754.png
    We shall choose the Delimiter option for our project:
    d02678c33bc2741a7094df20bba03e67.png

  • We shall action the same for the Priority table as well.

  • Rename columns (e.g., convert date fields to the "Date" format).
    582554101759de73d9569bb63d2f821e.png

  • Resolution Time(Days)  Standard SLA is 3.5 anything that is under this period that is within SLA otherwise it is outside SLA.

    To implement this in the table we can add a custom column with a custom column formula.
    dddc1836a2f07e30f4df9d80310cb928.png
    29333b2201d31f53bf1d82d61ff64d83.pngchange Data Type to text 5aad0ad5b8775e2c1bece98c0d15bc86.png

TRANSFORM IT_Agents Table :

  • Full Name : There are columns that have aberrations for either the first name or the last name.
    b94ee0fd5cf649bd728ccf8e0918642a.png

  • So , to make the process easier we shall remove the complete Full Name  field and use the email field and Delimiter function to extract first name and last name columns.
    fb65e91e2437ec7f92c6b2a4c4a35adb.png

  • In order to retrieve last name  we shall select extract Text between delimiters :
    59a31f2e9bcaad5cb5cb2eac2384777a.png

  • Remove unnecessary spaces using Trim and Clean options Under Format tab.
    4eee75bef08f06edc9ce7dc493c3c4ff.pnge552f89f276034d574fbb9edb9385de9.png

  • We shall also capitalize each words so that the entered data is standardized.
    7cb4ee524aa985c5a4ef952ad3ccefcf.png

  • Now we shall merge both the first name and last name columns.
    efd8edf943b375e8331b9f9c38155f3b.png

  • We shall create a new column for the Date of birth as well since the year , month and date are three separate columns currently
    636004a5e29fe9b775ab8925588411a6.png
    Ensure to change the data type of the new Date of birth column to 
    cf826e65cd2452a72a5cc1540b61ab12.png

  • We shall now create an Age column that showcases the age as of at 9/06/2025
    f8b0e8682adfbcd18cfcb772141cd54a.png

    Correcting the formatting for the age column

    1369dc97511e730b35365592b755e18d.png

  • Click "Close & Apply" to save changes.

50d2469f05577625ed082e4a2a93480f.png

We can have a summarized view of each column by enabling the following options from the view tab
960ce45881efc3cd93733cfff7bd2226.png

5. Create Relationships Between Tables

  • Now for any data that is date based it is best practice to create a Calendar table  which would enable us to draw relationships between all the other tables.
  • Go to the Model View (third icon on the left panel).
  • Create a new Calendar Table

    2f796a6cbcc8ee629c19af067fcff56a.png
  • We shall now create a relation between the Date column in the Calendar table and the Date column in the Tickets table as shown below :

    e9e0eb4b67ef3fa92b8522b12e4d8924.png

 

One out of many ways could be instead of including both the priority key and priority value both in a single table, we can create a second table that has all the corresponding priority value that relate to each of the priority key values.

We can action the same for the Severity key and Severity Value as well.

As a result we shall only have the columns for Severity and Priority key in the Tickets table , we shall have two separate tables one of the Severity values for each key value and one of Priority values for each corresponding key values.

Lets get to it : 
First we shall action it for the Severity Table 
a9f65121ae73773c3b43c938229419fd.png

Now, we shall action the same for the Priority Table.a9bb11c207ac8b61f299960a3badd275.png

  • Remove the Severity and Priority columns from the Tickets table as their corresponding values can be derived from the two new tables that we have created.
    054a62ebebd6c38952614f06f2a9b8aa.png
  • Click Close and Apply.

6. Create Measures for Analysis

  • Navigate to the Report View , Enter data and Name it Measures_Table

    f253ded0a4e821b71fa8ee66ee854696.png

🎫🎟️TICKETS DASHBOARD

7. Customizing the Dashboard:

  1. **Canvas Size : 1080 px X 1920 px
    737886f865dfcc352e2af55f906a24aa.png
    **
  2. **Customise the background and give it a Title:
    22b96bffddc84d5eeda1f546c1ab0009.png
    **
  3. **Slicer Visual:
    ac7d2089a204b17d427df0b4f5645a1e.png

    **Change the format to drop down and add padding:
    2648ee5caa08916eccef4d538e6daf5d.png
    Post all formatting the Year Slicer should appear similar to:
    8df549d6c8c49a4b5f417217837849d9.png
  4. **Duplicate the Year Slicer to create Quater , Month  and Day
    8c6959e00270fd9a3129da8aec0d876f.png
    **

 

8. Creating Measures

  1. Total Tickets

    Total Tickets = DISTINCTCOUNT(Tickets[ID Ticket])
    Total Tickets = DISTINCTCOUNT(Tickets[ID Ticket])
    29aabd7524b480a87cb077c071578ab1.png

  2. Average Resolution Time  ART

    Average Resolution Time = AVERAGE(Tickets[Resolution Time (Days)])
    Average Resolution Time = AVERAGE(Tickets[Resolution Time (Days)])

  3. Average Satisfaction Rate  ASR

    Average Satisfaction Rate = AVERAGE(Tickets[Satisfaction Rate])
    Average Satisfaction Rate = AVERAGE(Tickets[Satisfaction Rate])

  4. Within SLA

    Within SLA = CALCULATE([Total Tickets], Tickets[SLA Status] = "Within SLA")
    Within SLA = CALCULATE([Total Tickets], Tickets[SLA Status] = "Within SLA")

  5. ****SLA Breached

    SLA Breached = CALCULATE([Total Tickets], Tickets[SLA Status]="SLA Breached")
    SLA Breached = CALCULATE([Total Tickets], Tickets[SLA Status]="SLA Breached")

     

  6. SLA Compliant

    SLA Compliant = DIVIDE([Within SLA], [Total Tickets])
    SLA Compliant = DIVIDE([Within SLA], [Total Tickets])

    d62a9edf5f202e9967264bae6b7f188a.png

  7. SLA Non-compliant

    SLA Non-Compliant = DIVIDE([SLA Breached], [Total Tickets])
    SLA Non-Compliant = DIVIDE([SLA Breached], [Total Tickets])

    This has to be in percentage as well so action it similar to above.

9. Create a Dashboard (Report View)

  1. Switch to Report View (first icon on the left).

  2. Click "Insert" → "Card Visual": Add all the metrics from Measure_table
    af7e63c281eb85c7cc4d53ce1eea5ee0.png

  3. Formatted the Dashboard and added more effects to make it uniform:
    5f6d0db00d42a95f824c91b521ff89de.png

  4. Click "Insert" → "Bar Chart":

    TOTAL TICKETS BY SEVERITY

    • Drag "Total Tickets" to the X-axis and "Severity" to the Y-axis.
    • Make the necessary formatting changes to the chart.
      1d1b6e58564d93c8d73e0290c4fc9840.png

      TOTAL TICKETS BY PRIORITY
    • Copy the Bar chart that we created and change the Y-Axis to Priority.
    • Also change the chart type to 306ed54f6ae18c0a335da36f4d6aa0fb.png.
    • Once we change the chart type to Column instead of Bar, notice that X and Y axis are interchanged.
      b94a591ded3a1bca4d887436e1ce004f.png

  5. WATERFALL CHART based on the Ticket Category

    TOTAL TICKETS by Year and Request Category

    • Copy the column chart so that we are not required to repeat the formatting for the chart.

    311594da27c2f5d2c8ead610baa1db30.png Ascending chart will appear similar to  !\[39ccee4d5c4537944df6af36a55b0a78.png\](:/e8ae3fe6cbb04aa886a7cda0d840d6c9)

  6. TOTAL TICKETS by Quarter, Month and  Request Category - AREA CHART
     32d8795f8e6491a244f262d6b5906726.png

 

 

FINAL OVERVIEW OF THE TICKETS DASHBOARD :

0d106ae0319ae38271988cfe90762df9.png

IT AGENTS DASHBOARD

  1. Duplicate the Dash board above and Rename it to IT Agents Dashboard.

Creating Measures

  1. Total IT Agents

    Total IT Agents= DISTINCTCOUNT(Tickets[Agent ID])
    Total IT Agents= DISTINCTCOUNT(Tickets[Agent ID])

    97d94c44402e5160f6a10d3e5598bcf2.png

  2. Total Employees

    Total Employees = DISTINCTCOUNT(Tickets[Employee ID]
    Total Employees = DISTINCTCOUNT(Tickets[Employee ID]

  3. Average Age

    Average Age = AVERAGE(IT_Agents[Age]
    Average Age = AVERAGE(IT_Agents[Age]

  4. Agent Rating

    Agent Rating =
SWITCH(
TRUE(),
[Average Satisfaction Rate] < 4, REPT(UNICHAR(9733),3) & REPT(UNICHAR(9734),2),
[Average Satisfaction Rate] < 5, REPT(UNICHAR(9733),4) & REPT(UNICHAR(9734),1)
)
    Agent Rating =
SWITCH(
TRUE(),
[Average Satisfaction Rate] < 4, REPT(UNICHAR(9733),3) & REPT(UNICHAR(9734),2),
[Average Satisfaction Rate] < 5, REPT(UNICHAR(9733),4) & REPT(UNICHAR(9734),1)
)

 

VISUAL DASHBOARD - IT Agents

  1. Text Card(New) : For Average Age, Total Employees and Total IT Agents 
    d258554809b1e5ab0a1443915d385104.png
  2. Table Visual : 
    52be5f1c533b033eadfcaa3cd30c5927.png

 

SMART NARRATIVE

What is Smart Narrator (Smart Narrative)?
The Smart Narrative feature in Power BI helps create dynamic, AI-generated text summaries of your data visualizations. Instead of manually writing out insights, Power BI can automatically generate explanations, key trends, and data highlights from your visuals or entire report pages.

It essentially acts like a storyteller for your data.

0661c4aea2a5be101b2ca706439ad319.png